CREATE PROCEDURE Profile_FindAll
AS
BEGIN
    Select 
    ProfileID,
    PersonID,
    FirstName,
    MiddleName,
    LastName,
    DisplayName,
    DateOfBirth,
    Email,
    AddressId,
    Avatar,
    AboutMe,
    IsPrivate
    from Profile
END
GO

CREATE PROCEDURE Profile_FindById
@ProfileID int
AS
BEGIN
    Select * from Profile where ProfileID = @ProfileID
END
GO

CREATE PROCEDURE Profile_FindByName
@name nvarchar(50)
AS
BEGIN
    Select * from Profile where ProfileID = @name
END
GO

CREATE PROCEDURE Profile_Insert
        @ProfileID int ,
        @PersonID int ,
        @FirstName nvarchar (50),
        @MiddleName nvarchar (50),
        @LastName nvarchar (50),
        @DisplayName nvarchar (50),
        @DateOfBirth datetime ,
        @Email nvarchar (50),
        @AddressId nvarchar (50),
        @Avatar varchar (200),
        @AboutMe nvarchar (-1),
        @IsPrivate bit 
AS
BEGIN
INSERT INTO Profile
(
        ProfileID,
        PersonID,
        FirstName,
        MiddleName,
        LastName,
        DisplayName,
        DateOfBirth,
        Email,
        AddressId,
        Avatar,
        AboutMe,
        IsPrivate
)
VALUES
(
        @ProfileID,
        @PersonID,
        @FirstName,
        @MiddleName,
        @LastName,
        @DisplayName,
        @DateOfBirth,
        @Email,
        @AddressId,
        @Avatar,
        @AboutMe,
        @IsPrivate
)
select @ProfileID = @@IDENTITY
END
GO

CREATE PROCEDURE Profile_Update
        @ProfileID int ,
        @PersonID int ,
        @FirstName nvarchar (50),  
        @MiddleName nvarchar (50),  
        @LastName nvarchar (50),  
        @DisplayName nvarchar (50),  
        @DateOfBirth datetime ,
        @Email nvarchar (50),  
        @AddressId nvarchar (50),  
        @Avatar varchar (200),  
        @AboutMe nvarchar (-1),  
        @IsPrivate bit
AS
BEGIN
UPDATE  Profile SET
    PersonID = @PersonID,
    FirstName = @FirstName,
    MiddleName = @MiddleName,
    LastName = @LastName,
    DisplayName = @DisplayName,
    DateOfBirth = @DateOfBirth,
    Email = @Email,
    AddressId = @AddressId,
    Avatar = @Avatar,
    AboutMe = @AboutMe,
    IsPrivate = @IsPrivate
    WHERE ProfileID = @ProfileID
END
GO

CREATE PROCEDURE Profile_Delete
    @ProfileID int
AS
BEGIN
    DELETE FROM Profile where ProfileID = @ProfileID
END
GO

CREATE PROCEDURE Profile_Paging
    @PageSize int, 
    @PageNumber int
    AS 
    declare @RowStart int
    declare @RowEnd int
    if @PageNumber > 0 
    BEGIN	
    set @PageNumber = @PageNumber - 1
    set @RowStart = @PageSize * @PageNumber + 1;
    set @RowEnd = @RowStart  + @PageSize - 1;
    SELECT TotalRowCount = COUNT(ProfileID) FROM Profile;
    WITH MyTable as 
    (
    SELECT * ,  ROW_NUMBER() OVER (order by SortIndex) as RowNumber From  Profile
    )
    SELECT * from MyTable where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
GO

CREATE proc Profile_SetActive
@ProfileID int
AS
declare @Type bit
SELECT @Type =  IsActive from Profile where ProfileID = @ProfileID
BEGIN
IF(@Type = 1)
Update Profile set IsActive = 0 where ProfileID = @ProfileID
else
Update Profile set IsActive = 1 where ProfileID = @ProfileID
END
GO

CREATE proc Profile_SetSortIndex
@ProfileID int, 
@Type int
AS
declare @CurrentSort int
select @CurrentSort = SortIndex from  Profile where ProfileID = @ProfileID
BEGIN
IF(@Type = 1)
Update Profile set SortIndex =  @CurrentSort + 1 where ProfileID = @ProfileID
else
Update Profile set SortIndex =  @CurrentSort - 1 where ProfileID = @ProfileID
END
GO

CREATE PROCEDURE Profile_RemoveSelectedItem
    @ProfileID int
AS
BEGIN
    UPDATE Profile SET IsDeleted = 1 WHERE ProfileID = @ProfileID
END
GO

CREATE PROCEDURE Profile_RestoreSelectedItem
    @ProfileID int
AS
BEGIN
    UPDATE Profile SET IsDeleted = 0 WHERE ProfileID = @ProfileID
END
GO

CREATE PROCEDURE Profile_RemoveAll
    @ProfileID int
AS
BEGIN
    UPDATE Profile SET IsDeleted = 1
END
GO

CREATE PROCEDURE Profile_RestoreAll
    @ProfileID int
AS
BEGIN
    UPDATE Profile SET IsDeleted = 0
END
GO

